﻿CREATE PROCEDURE [dbo].[SearchByImage]
	@ImageAnalysisId BIGINT,
	@Range FLOAT, 
	@HueRange FLOAT
AS
	-- Set the internal thresholds for section lienency and percent range
	DECLARE @SectionRange INT = 1;
	DECLARE @PercentRange FLOAT = 5;

	WITH SCS ([S_DefinitionId], [S_AverageDistance], [S_SectionX], [S_SectionY], [S_PercentWithinDistance], [S_AverageHueDistance]) AS
	(
		-- Get the source values from the source image analysis ID
		SELECT [DefinitionId], [AverageDistance], [SectionX], [SectionY], [PercentWithinDistance], [AverageHueDistance]
		FROM[dbo].[ColorSpectrum]
		WHERE [ImageAnalysisId] = @ImageAnalysisId
	), AllData AS 
	(
		-- For each color definition, make sure the definition matches the source, within the section range for X and Y,
		-- Is within the percent ditance ranage, average distance range, and the hue range if applicable
		SELECT [ImageAnalysisId], [SectionX], [SectionY], [DefinitionId], ABS(SCS.[S_AverageDistance] - CS.[AverageDistance]) AS [Rank]
		FROM SCS
		INNER JOIN[dbo].[ColorSpectrum] CS ON CS.[DefinitionId] = SCS.[S_DefinitionId]
		WHERE CS.[DefinitionId] = 0 
		-- Hue Does Not Apply To Black or White
		AND CS.[AverageDistance] BETWEEN SCS.[S_AverageDistance] - @Range AND SCS.[S_AverageDistance] + @Range
		AND CS.[SectionX] BETWEEN SCS.[S_SectionX] - @SectionRange AND SCS.[S_SectionX] + @SectionRange
		AND CS.[SectionY] BETWEEN SCS.[S_SectionY] - @SectionRange AND SCS.[S_SectionY] + @SectionRange
		AND CS.[PercentWithinDistance] BETWEEN SCS.[S_PercentWithinDistance] - @PercentRange AND SCS.[S_PercentWithinDistance] + @PercentRange
		UNION ALL
		SELECT [ImageAnalysisId], [SectionX], [SectionY], [DefinitionId], ABS(SCS.[S_AverageDistance] - CS.[AverageDistance]) AS [Rank]
		FROM SCS
		INNER JOIN[dbo].[ColorSpectrum] CS ON CS.[DefinitionId] = SCS.[S_DefinitionId]
		WHERE CS.[DefinitionId] = 1 
		AND CS.[AverageHueDistance] BETWEEN SCS.[S_AverageHueDistance] - @HueRange AND SCS.[S_AverageHueDistance] + @HueRange
		AND CS.[AverageDistance] BETWEEN SCS.[S_AverageDistance] - @Range AND SCS.[S_AverageDistance] + @Range
		AND CS.[SectionX] BETWEEN SCS.[S_SectionX] - @SectionRange AND SCS.[S_SectionX] + @SectionRange
		AND CS.[SectionY] BETWEEN SCS.[S_SectionY] - @SectionRange AND SCS.[S_SectionY] + @SectionRange
		AND CS.[PercentWithinDistance] BETWEEN SCS.[S_PercentWithinDistance] - @PercentRange AND SCS.[S_PercentWithinDistance] + @PercentRange
		UNION ALL
		SELECT [ImageAnalysisId], [SectionX], [SectionY], [DefinitionId], ABS(SCS.[S_AverageDistance] - CS.[AverageDistance]) AS [Rank]
		FROM SCS
		INNER JOIN[dbo].[ColorSpectrum] CS ON CS.[DefinitionId] = SCS.[S_DefinitionId]
		WHERE CS.[DefinitionId] = 2 
		AND CS.[AverageHueDistance] BETWEEN SCS.[S_AverageHueDistance] - @HueRange AND SCS.[S_AverageHueDistance] + @HueRange
		AND CS.[AverageDistance] BETWEEN SCS.[S_AverageDistance] - @Range AND SCS.[S_AverageDistance] + @Range
		AND CS.[SectionX] BETWEEN SCS.[S_SectionX] - @SectionRange AND SCS.[S_SectionX] + @SectionRange
		AND CS.[SectionY] BETWEEN SCS.[S_SectionY] - @SectionRange AND SCS.[S_SectionY] + @SectionRange
		AND CS.[PercentWithinDistance] BETWEEN SCS.[S_PercentWithinDistance] - @PercentRange AND SCS.[S_PercentWithinDistance] + @PercentRange
		UNION ALL
		SELECT [ImageAnalysisId], [SectionX], [SectionY], [DefinitionId], ABS(SCS.[S_AverageDistance] - CS.[AverageDistance]) AS [Rank]
		FROM SCS
		INNER JOIN[dbo].[ColorSpectrum] CS ON CS.[DefinitionId] = SCS.[S_DefinitionId]
		WHERE CS.[DefinitionId] = 3 
		AND CS.[AverageDistance] BETWEEN SCS.[S_AverageDistance] - @Range AND SCS.[S_AverageDistance] + @Range
		AND CS.[SectionX] BETWEEN SCS.[S_SectionX] - @SectionRange AND SCS.[S_SectionX] + @SectionRange
		AND CS.[SectionY] BETWEEN SCS.[S_SectionY] - @SectionRange AND SCS.[S_SectionY] + @SectionRange
		AND CS.[PercentWithinDistance] BETWEEN SCS.[S_PercentWithinDistance] - @PercentRange AND SCS.[S_PercentWithinDistance] + @PercentRange
		UNION ALL
		SELECT [ImageAnalysisId], [SectionX], [SectionY], [DefinitionId], ABS(SCS.[S_AverageDistance] - CS.[AverageDistance]) AS [Rank]
		FROM SCS
		INNER JOIN[dbo].[ColorSpectrum] CS ON CS.[DefinitionId] = SCS.[S_DefinitionId]
		WHERE CS.[DefinitionId] = 4 
		AND CS.[AverageHueDistance] BETWEEN SCS.[S_AverageHueDistance] - @HueRange AND SCS.[S_AverageHueDistance] + @HueRange
		AND CS.[AverageDistance] BETWEEN SCS.[S_AverageDistance] - @Range AND SCS.[S_AverageDistance] + @Range
		AND CS.[SectionX] BETWEEN SCS.[S_SectionX] - @SectionRange AND SCS.[S_SectionX] + @SectionRange
		AND CS.[SectionY] BETWEEN SCS.[S_SectionY] - @SectionRange AND SCS.[S_SectionY] + @SectionRange
		AND CS.[PercentWithinDistance] BETWEEN SCS.[S_PercentWithinDistance] - @PercentRange AND SCS.[S_PercentWithinDistance] + @PercentRange
		UNION ALL
		SELECT [ImageAnalysisId], [SectionX], [SectionY], [DefinitionId], ABS(SCS.[S_AverageDistance] - CS.[AverageDistance]) AS [Rank]
		FROM SCS
		INNER JOIN[dbo].[ColorSpectrum] CS ON CS.[DefinitionId] = SCS.[S_DefinitionId]
		WHERE CS.[DefinitionId] = 5 
		AND CS.[AverageHueDistance] BETWEEN SCS.[S_AverageHueDistance] - @HueRange AND SCS.[S_AverageHueDistance] + @HueRange
		AND CS.[AverageDistance] BETWEEN SCS.[S_AverageDistance] - @Range AND SCS.[S_AverageDistance] + @Range
		AND CS.[SectionX] BETWEEN SCS.[S_SectionX] - @SectionRange AND SCS.[S_SectionX] + @SectionRange
		AND CS.[SectionY] BETWEEN SCS.[S_SectionY] - @SectionRange AND SCS.[S_SectionY] + @SectionRange
		AND CS.[PercentWithinDistance] BETWEEN SCS.[S_PercentWithinDistance] - @PercentRange AND SCS.[S_PercentWithinDistance] + @PercentRange
		UNION ALL
		SELECT [ImageAnalysisId], [SectionX], [SectionY], [DefinitionId], ABS(SCS.[S_AverageDistance] - CS.[AverageDistance]) AS [Rank]
		FROM SCS
		INNER JOIN[dbo].[ColorSpectrum] CS ON CS.[DefinitionId] = SCS.[S_DefinitionId]
		WHERE CS.[DefinitionId] = 6 
		AND CS.[AverageHueDistance] BETWEEN SCS.[S_AverageHueDistance] - @HueRange AND SCS.[S_AverageHueDistance] + @HueRange
		AND CS.[AverageDistance] BETWEEN SCS.[S_AverageDistance] - @Range AND SCS.[S_AverageDistance] + @Range
		AND CS.[SectionX] BETWEEN SCS.[S_SectionX] - @SectionRange AND SCS.[S_SectionX] + @SectionRange
		AND CS.[SectionY] BETWEEN SCS.[S_SectionY] - @SectionRange AND SCS.[S_SectionY] + @SectionRange
		AND CS.[PercentWithinDistance] BETWEEN SCS.[S_PercentWithinDistance] - @PercentRange AND SCS.[S_PercentWithinDistance] + @PercentRange
		UNION ALL
		SELECT [ImageAnalysisId], [SectionX], [SectionY], [DefinitionId], ABS(SCS.[S_AverageDistance] - CS.[AverageDistance]) AS [Rank]
		FROM SCS
		INNER JOIN[dbo].[ColorSpectrum] CS ON CS.[DefinitionId] = SCS.[S_DefinitionId]
		WHERE CS.[DefinitionId] = 7 
		-- Hue Does Not Apply To Black or White
		AND CS.[AverageDistance] BETWEEN SCS.[S_AverageDistance] - @Range AND SCS.[S_AverageDistance] + @Range
		AND CS.[SectionX] BETWEEN SCS.[S_SectionX] - @SectionRange AND SCS.[S_SectionX] + @SectionRange
		AND CS.[SectionY] BETWEEN SCS.[S_SectionY] - @SectionRange AND SCS.[S_SectionY] + @SectionRange
		AND CS.[PercentWithinDistance] BETWEEN SCS.[S_PercentWithinDistance] - @PercentRange AND SCS.[S_PercentWithinDistance] + @PercentRange
	), ResultsPerDefinition AS
	(
		-- For each image analysis and color definition,
		-- Make sure that there are at least 8 matching sections
		SELECT [ImageAnalysisId], [DefinitionId], MAX([Rank]) AS [Rank]
		FROM AllData
		GROUP BY [ImageAnalysisId], [DefinitionId]
		HAVING COUNT(DISTINCT [SectionX]) >= 8
		AND COUNT(DISTINCT [SectionY]) >= 8
	), Results AS
	(
		-- Make sure there are all 8 color definitions for the image analysis record
		SELECT [ImageAnalysisId], MAX([Rank]) AS [Rank]
		FROM ResultsPerDefinition
		GROUP BY [ImageAnalysisId]
		HAVING COUNT(DISTINCT [DefinitionId]) = 8
	)
	-- Get the image data from the image analysis results
	-- Excluse the source image from the results and private images
	SELECT I.*
	FROM Results R
	INNER JOIN [dbo].[ImageAnalysis] IA ON R.[ImageAnalysisId] = IA.[Id]
	INNER JOIN [dbo].[Image] I ON IA.[ImageId] = I.[Id]
	WHERE IA.[Id] <> @ImageAnalysisId
	AND [IsFolderPrivate] = 0 AND [IsPrivate] = 0
	ORDER BY [Rank] ASC
RETURN 0
